using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Drawing;
using System.Drawing.Printing;
using System.Text;
using System.Windows.Forms;
using ctsu.Admin.IRBWebService;
using Microsoft.Practices.EnterpriseLibrary.Data;

using Microsoft.VisualBasic;
using ctsu.Data;

namespace ctsu.Admin.Forms
{
	public class FormIRBData : System.Windows.Forms.Form
	{
		private DataTable _dtAnalyzed;
		private DataTable _dtResults;
		private int _dupeCount;

		//for printing
		private PrintDialog _pDlg;
		private PrintDocument _pDoc;
		private int _page;
		private int _startRow;

		private System.Windows.Forms.GroupBox gbxCriteria;
		private System.Windows.Forms.RadioButton rdoDates;
		private System.Windows.Forms.RadioButton rdoNumber;
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.Label label2;
		private Syncfusion.Windows.Forms.Tools.DateTimePickerAdv EndDate;
		private System.Windows.Forms.Label label3;
		private Syncfusion.Windows.Forms.Tools.DateTimePickerAdv BeginDate;
		private System.Windows.Forms.Label label4;
		private System.Windows.Forms.TextBox Number;
		private System.Windows.Forms.Button btnGetData;
		private System.Windows.Forms.DataGrid dg;
		private System.Windows.Forms.Button btnAnalyze;
		private System.Windows.Forms.GroupBox groupBox1;
		private System.Windows.Forms.RadioButton rdoSortDate;
		private System.Windows.Forms.RadioButton rdoSortNumber;
		private System.Windows.Forms.Panel panel1;
		private System.Windows.Forms.Splitter splitter1;
		private System.Windows.Forms.DataGrid dgAnal;
		private System.Windows.Forms.Button btnPrint;
		private System.Windows.Forms.Button btnUpdate;
		private System.Windows.Forms.Button btnRemove;
		private System.Windows.Forms.Button btnExit;
		private System.Windows.Forms.Button btnRemoveExceptions;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

		public FormIRBData()
		{

			InitializeComponent();

			InitializeResultsTable();   //create the results table
			InitializeResultsTableStyle();   //assign datasource to grid and size columns
			InitializeAnalyzedTable();  //create the table columns			
			InitializeAnalyzedTableStyle();   //assign datasource to grid and size columns
			BeginDate.Value = DateTime.Today;
			BeginDate.IsNullDate = true;
			EndDate.Value = DateTime.Today;
			EndDate.IsNullDate = true;

		}

		private void InitializeResultsTableStyle()
		{
			//size grid columns			
			dgAnal.DataSource = _dtResults;

			DataGridTableStyle dgts = new DataGridTableStyle();
			dgts.MappingName = "Results";
			dgAnal.TableStyles.Add(dgts);

			dgts.GridColumnStyles[0].Width = 40;
			dgts.GridColumnStyles[0].HeaderText = _dtResults.Columns[0].Caption;
			dgts.GridColumnStyles[1].Width = 95;
			dgts.GridColumnStyles[1].HeaderText = _dtResults.Columns[1].Caption;
			dgts.GridColumnStyles[2].Width = 110;
			dgts.GridColumnStyles[2].HeaderText = _dtResults.Columns[2].Caption;
			dgts.GridColumnStyles[3].Width = 85;
			dgts.GridColumnStyles[3].HeaderText = _dtResults.Columns[3].Caption;
			dgts.GridColumnStyles[4].Width = 500;
			dgts.GridColumnStyles[4].HeaderText = _dtResults.Columns[3].Caption;

		}

		private void InitializeAnalyzedTableStyle()
		{
			//size grid columns			
			dgAnal.DataSource = _dtAnalyzed;

			DataGridTableStyle dgts = new DataGridTableStyle();
			dgts.MappingName = "Analyzed";
			dgAnal.TableStyles.Add(dgts);

			dgts.GridColumnStyles[0].Width = 40;
			dgts.GridColumnStyles[0].HeaderText = _dtAnalyzed.Columns[0].Caption;
			dgts.GridColumnStyles[1].Width = 95;
			dgts.GridColumnStyles[1].HeaderText = _dtAnalyzed.Columns[1].Caption;
			dgts.GridColumnStyles[2].Width = 100;
			dgts.GridColumnStyles[2].HeaderText = _dtAnalyzed.Columns[2].Caption;
			dgts.GridColumnStyles[3].Width = 75;
			dgts.GridColumnStyles[3].HeaderText = _dtAnalyzed.Columns[3].Caption;
			dgts.GridColumnStyles[4].Width = 250;
			dgts.GridColumnStyles[4].HeaderText = _dtAnalyzed.Columns[4].Caption;
			dgts.GridColumnStyles[5].Width = 200;
			dgts.GridColumnStyles[5].HeaderText = _dtAnalyzed.Columns[5].Caption;
			dgts.GridColumnStyles[6].Width = 75;
			dgts.GridColumnStyles[6].HeaderText = _dtAnalyzed.Columns[6].Caption;


		}

		private void InitializeResultsTable()
		{
			_dtResults = new DataTable("Results");
			DataColumn dc = new DataColumn("ID", typeof(string));
			dc.Caption = "ID";
			_dtResults.Columns.Add(dc);
			dc = new DataColumn("ProtocolNumber", typeof(String));
			dc.Caption = "Protocol Number";
			_dtResults.Columns.Add(dc);
			dc = new DataColumn("Action", typeof(String));
			dc.Caption = "Action";
			_dtResults.Columns.Add(dc);
			dc = new DataColumn("Result", typeof(String));
			dc.Caption = "Result";
			_dtResults.Columns.Add(dc);
			dc = new DataColumn("Message", typeof(String));
			dc.Caption = "Message";
			_dtResults.Columns.Add(dc);
			dc = new DataColumn("IRBApproval", typeof(DateTime));
			dc.Caption = "IRB Approval";
			_dtResults.Columns.Add(dc);
		}

		private void InitializeAnalyzedTable()
		{
			_dtAnalyzed = new DataTable("Analyzed");
			DataColumn dc = new DataColumn("ID", typeof(string));
			dc.Caption = "ID";
			dc.Unique = false;
			_dtAnalyzed.Columns.Add(dc);
			dc = new DataColumn("ProtocolNumber", typeof(String));
			dc.Caption = "Protocol Number";
			_dtAnalyzed.Columns.Add(dc);
			dc = new DataColumn("ReviewType", typeof(String));
			dc.Caption = "Review Type";
			_dtAnalyzed.Columns.Add(dc);
			dc = new DataColumn("Status", typeof(String));
			_dtAnalyzed.Columns.Add(dc);
			dc = new DataColumn("Message", typeof(String));
			_dtAnalyzed.Columns.Add(dc);
			dc = new DataColumn("ComparingData", typeof(String));
			dc.Caption = "Comparing Data (Existing / IRB)";
			_dtAnalyzed.Columns.Add(dc);
			dc = new DataColumn("IRBApproval", typeof(DateTime));
			dc.Caption = "IRB Approval";
			_dtAnalyzed.Columns.Add(dc);
			dc = new DataColumn("ReviewCode", typeof(String));
			dc.Caption = "Review Code";
			_dtAnalyzed.Columns.Add(dc);

			DataColumn[] dca = new DataColumn[1];
			dca[0] = this._dtAnalyzed.Columns[1];

			this._dtAnalyzed.PrimaryKey = dca;
		}

		protected override void Dispose(bool disposing)
		{
			if (disposing)
			{
				if (components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose(disposing);
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			this.gbxCriteria = new System.Windows.Forms.GroupBox();
			this.groupBox1 = new System.Windows.Forms.GroupBox();
			this.rdoSortNumber = new System.Windows.Forms.RadioButton();
			this.rdoSortDate = new System.Windows.Forms.RadioButton();
			this.btnGetData = new System.Windows.Forms.Button();
			this.Number = new System.Windows.Forms.TextBox();
			this.label4 = new System.Windows.Forms.Label();
			this.label2 = new System.Windows.Forms.Label();
			this.EndDate = new Syncfusion.Windows.Forms.Tools.DateTimePickerAdv();
			this.label3 = new System.Windows.Forms.Label();
			this.BeginDate = new Syncfusion.Windows.Forms.Tools.DateTimePickerAdv();
			this.label1 = new System.Windows.Forms.Label();
			this.rdoNumber = new System.Windows.Forms.RadioButton();
			this.rdoDates = new System.Windows.Forms.RadioButton();
			this.dg = new System.Windows.Forms.DataGrid();
			this.btnAnalyze = new System.Windows.Forms.Button();
			this.panel1 = new System.Windows.Forms.Panel();
			this.dgAnal = new System.Windows.Forms.DataGrid();
			this.splitter1 = new System.Windows.Forms.Splitter();
			this.btnPrint = new System.Windows.Forms.Button();
			this.btnUpdate = new System.Windows.Forms.Button();
			this.btnRemove = new System.Windows.Forms.Button();
			this.btnExit = new System.Windows.Forms.Button();
			this.btnRemoveExceptions = new System.Windows.Forms.Button();
			this.gbxCriteria.SuspendLayout();
			this.groupBox1.SuspendLayout();
			((System.ComponentModel.ISupportInitialize)(this.EndDate)).BeginInit();
			((System.ComponentModel.ISupportInitialize)(this.EndDate.Calendar)).BeginInit();
			((System.ComponentModel.ISupportInitialize)(this.BeginDate)).BeginInit();
			((System.ComponentModel.ISupportInitialize)(this.BeginDate.Calendar)).BeginInit();
			((System.ComponentModel.ISupportInitialize)(this.dg)).BeginInit();
			this.panel1.SuspendLayout();
			((System.ComponentModel.ISupportInitialize)(this.dgAnal)).BeginInit();
			this.SuspendLayout();
			// 
			// gbxCriteria
			// 
			this.gbxCriteria.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left)
				| System.Windows.Forms.AnchorStyles.Right)));
			this.gbxCriteria.Controls.Add(this.groupBox1);
			this.gbxCriteria.Controls.Add(this.btnGetData);
			this.gbxCriteria.Controls.Add(this.Number);
			this.gbxCriteria.Controls.Add(this.label4);
			this.gbxCriteria.Controls.Add(this.label2);
			this.gbxCriteria.Controls.Add(this.EndDate);
			this.gbxCriteria.Controls.Add(this.label3);
			this.gbxCriteria.Controls.Add(this.BeginDate);
			this.gbxCriteria.Controls.Add(this.label1);
			this.gbxCriteria.Controls.Add(this.rdoNumber);
			this.gbxCriteria.Controls.Add(this.rdoDates);
			this.gbxCriteria.Location = new System.Drawing.Point(8, 0);
			this.gbxCriteria.Name = "gbxCriteria";
			this.gbxCriteria.Size = new System.Drawing.Size(864, 120);
			this.gbxCriteria.TabIndex = 0;
			this.gbxCriteria.TabStop = false;
			this.gbxCriteria.Text = "Criteria";
			// 
			// groupBox1
			// 
			this.groupBox1.Controls.Add(this.rdoSortNumber);
			this.groupBox1.Controls.Add(this.rdoSortDate);
			this.groupBox1.Location = new System.Drawing.Point(712, 8);
			this.groupBox1.Name = "groupBox1";
			this.groupBox1.Size = new System.Drawing.Size(144, 72);
			this.groupBox1.TabIndex = 11;
			this.groupBox1.TabStop = false;
			this.groupBox1.Text = "Sort Order";
			// 
			// rdoSortNumber
			// 
			this.rdoSortNumber.Location = new System.Drawing.Point(8, 40);
			this.rdoSortNumber.Name = "rdoSortNumber";
			this.rdoSortNumber.Size = new System.Drawing.Size(128, 24);
			this.rdoSortNumber.TabIndex = 1;
			this.rdoSortNumber.Text = "Protocol Number";
			// 
			// rdoSortDate
			// 
			this.rdoSortDate.Checked = true;
			this.rdoSortDate.Location = new System.Drawing.Point(8, 16);
			this.rdoSortDate.Name = "rdoSortDate";
			this.rdoSortDate.Size = new System.Drawing.Size(120, 24);
			this.rdoSortDate.TabIndex = 0;
			this.rdoSortDate.TabStop = true;
			this.rdoSortDate.Text = "Approval Date";
			// 
			// btnGetData
			// 
			this.btnGetData.Location = new System.Drawing.Point(8, 88);
			this.btnGetData.Name = "btnGetData";
			this.btnGetData.Size = new System.Drawing.Size(128, 23);
			this.btnGetData.TabIndex = 10;
			this.btnGetData.Text = "Retrieve IRB Data";
			this.btnGetData.Click += new System.EventHandler(this.btnGetData_Click);
			// 
			// Number
			// 
			this.Number.Location = new System.Drawing.Point(440, 64);
			this.Number.Name = "Number";
			this.Number.Size = new System.Drawing.Size(128, 22);
			this.Number.TabIndex = 9;
			this.Number.Text = "";
			// 
			// label4
			// 
			this.label4.Location = new System.Drawing.Point(328, 64);
			this.label4.Name = "label4";
			this.label4.Size = new System.Drawing.Size(112, 16);
			this.label4.TabIndex = 8;
			this.label4.Text = "Protocol Number:";
			// 
			// label2
			// 
			this.label2.Location = new System.Drawing.Point(480, 40);
			this.label2.Name = "label2";
			this.label2.Size = new System.Drawing.Size(64, 16);
			this.label2.TabIndex = 7;
			this.label2.Text = "End date:";
			// 
			// EndDate
			// 
			// 
			// EndDate.Calendar
			// 
			this.EndDate.Calendar.AllowMultipleSelection = false;
			this.EndDate.Calendar.Culture = new System.Globalization.CultureInfo("");
			this.EndDate.Calendar.DayNamesFont = new System.Drawing.Font("Verdana", 8F);
			this.EndDate.Calendar.DaysFont = new System.Drawing.Font("Verdana", 8F);
			this.EndDate.Calendar.DaysHeaderInterior = new Syncfusion.Drawing.BrushInfo(Syncfusion.Drawing.GradientStyle.Vertical, System.Drawing.Color.PeachPuff, System.Drawing.Color.AntiqueWhite);
			this.EndDate.Calendar.Dock = System.Windows.Forms.DockStyle.Fill;
			this.EndDate.Calendar.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
			this.EndDate.Calendar.Location = new System.Drawing.Point(0, 0);
			this.EndDate.Calendar.MinValue = new System.DateTime(1753, 1, 1, 0, 0, 0, 0);
			this.EndDate.Calendar.Name = "monthCalendar";
			// 
			// EndDate.Calendar.NoneButton
			// 
			this.EndDate.Calendar.NoneButton.Location = new System.Drawing.Point(194, 0);
			this.EndDate.Calendar.Size = new System.Drawing.Size(266, 174);
			this.EndDate.Calendar.SizeToFit = true;
			this.EndDate.Calendar.Style = Syncfusion.Windows.Forms.VisualStyle.Default;
			this.EndDate.Calendar.TabIndex = 0;
			// 
			// EndDate.Calendar.TodayButton
			// 
			this.EndDate.Calendar.TodayButton.Location = new System.Drawing.Point(0, 0);
			this.EndDate.Calendar.TodayButton.Size = new System.Drawing.Size(194, 20);
			this.EndDate.CalendarFont = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
			this.EndDate.Format = System.Windows.Forms.DateTimePickerFormat.Short;
			this.EndDate.IsNullDate = true;
			this.EndDate.Location = new System.Drawing.Point(544, 40);
			this.EndDate.Name = "EndDate";
			this.EndDate.NullString = "";
			this.EndDate.ShowCheckBox = false;
			this.EndDate.Size = new System.Drawing.Size(104, 20);
			this.EndDate.TabIndex = 6;
			// 
			// label3
			// 
			this.label3.Location = new System.Drawing.Point(288, 40);
			this.label3.Name = "label3";
			this.label3.Size = new System.Drawing.Size(72, 19);
			this.label3.TabIndex = 5;
			this.label3.Text = "Begin date:";
			// 
			// BeginDate
			// 
			// 
			// BeginDate.Calendar
			// 
			this.BeginDate.Calendar.AllowMultipleSelection = false;
			this.BeginDate.Calendar.Culture = new System.Globalization.CultureInfo("");
			this.BeginDate.Calendar.DayNamesFont = new System.Drawing.Font("Verdana", 8F);
			this.BeginDate.Calendar.DaysFont = new System.Drawing.Font("Verdana", 8F);
			this.BeginDate.Calendar.DaysHeaderInterior = new Syncfusion.Drawing.BrushInfo(Syncfusion.Drawing.GradientStyle.Vertical, System.Drawing.Color.PeachPuff, System.Drawing.Color.AntiqueWhite);
			this.BeginDate.Calendar.Dock = System.Windows.Forms.DockStyle.Fill;
			this.BeginDate.Calendar.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
			this.BeginDate.Calendar.Location = new System.Drawing.Point(0, 0);
			this.BeginDate.Calendar.MinValue = new System.DateTime(1753, 1, 1, 0, 0, 0, 0);
			this.BeginDate.Calendar.Name = "monthCalendar";
			// 
			// BeginDate.Calendar.NoneButton
			// 
			this.BeginDate.Calendar.NoneButton.Location = new System.Drawing.Point(194, 0);
			this.BeginDate.Calendar.Size = new System.Drawing.Size(266, 174);
			this.BeginDate.Calendar.SizeToFit = true;
			this.BeginDate.Calendar.Style = Syncfusion.Windows.Forms.VisualStyle.Default;
			this.BeginDate.Calendar.TabIndex = 0;
			// 
			// BeginDate.Calendar.TodayButton
			// 
			this.BeginDate.Calendar.TodayButton.Location = new System.Drawing.Point(0, 0);
			this.BeginDate.Calendar.TodayButton.Size = new System.Drawing.Size(194, 20);
			this.BeginDate.CalendarFont = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
			this.BeginDate.Format = System.Windows.Forms.DateTimePickerFormat.Short;
			this.BeginDate.IsNullDate = true;
			this.BeginDate.Location = new System.Drawing.Point(368, 40);
			this.BeginDate.Name = "BeginDate";
			this.BeginDate.NullString = "";
			this.BeginDate.ShowCheckBox = false;
			this.BeginDate.Size = new System.Drawing.Size(104, 20);
			this.BeginDate.TabIndex = 4;
			// 
			// label1
			// 
			this.label1.Location = new System.Drawing.Point(8, 16);
			this.label1.Name = "label1";
			this.label1.Size = new System.Drawing.Size(280, 19);
			this.label1.TabIndex = 3;
			this.label1.Text = "Basic criteria is for gcrc approved protocols.";
			// 
			// rdoNumber
			// 
			this.rdoNumber.Location = new System.Drawing.Point(8, 64);
			this.rdoNumber.Name = "rdoNumber";
			this.rdoNumber.Size = new System.Drawing.Size(320, 19);
			this.rdoNumber.TabIndex = 2;
			this.rdoNumber.Text = "By Protocol Number (returns a specified protocol)";
			// 
			// rdoDates
			// 
			this.rdoDates.Checked = true;
			this.rdoDates.Location = new System.Drawing.Point(8, 40);
			this.rdoDates.Name = "rdoDates";
			this.rdoDates.Size = new System.Drawing.Size(280, 19);
			this.rdoDates.TabIndex = 1;
			this.rdoDates.TabStop = true;
			this.rdoDates.Text = "By Date (returns protocols for a date range)";
			// 
			// dg
			// 
			this.dg.CaptionBackColor = System.Drawing.Color.White;
			this.dg.CaptionForeColor = System.Drawing.Color.RoyalBlue;
			this.dg.CaptionText = "IRB Data";
			this.dg.DataMember = "";
			this.dg.Dock = System.Windows.Forms.DockStyle.Top;
			this.dg.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.dg.Location = new System.Drawing.Point(0, 0);
			this.dg.Name = "dg";
			this.dg.Size = new System.Drawing.Size(872, 176);
			this.dg.TabIndex = 2;
			// 
			// btnAnalyze
			// 
			this.btnAnalyze.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
			this.btnAnalyze.Enabled = false;
			this.btnAnalyze.Location = new System.Drawing.Point(0, 496);
			this.btnAnalyze.Name = "btnAnalyze";
			this.btnAnalyze.Size = new System.Drawing.Size(128, 23);
			this.btnAnalyze.TabIndex = 11;
			this.btnAnalyze.Text = "Analyze IRB Data";
			this.btnAnalyze.Click += new System.EventHandler(this.btnAnalyze_Click);
			// 
			// panel1
			// 
			this.panel1.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
				| System.Windows.Forms.AnchorStyles.Left)
				| System.Windows.Forms.AnchorStyles.Right)));
			this.panel1.Controls.Add(this.dgAnal);
			this.panel1.Controls.Add(this.splitter1);
			this.panel1.Controls.Add(this.dg);
			this.panel1.Location = new System.Drawing.Point(0, 120);
			this.panel1.Name = "panel1";
			this.panel1.Size = new System.Drawing.Size(872, 368);
			this.panel1.TabIndex = 12;
			// 
			// dgAnal
			// 
			this.dgAnal.CaptionBackColor = System.Drawing.Color.White;
			this.dgAnal.CaptionForeColor = System.Drawing.Color.RoyalBlue;
			this.dgAnal.CaptionText = "Analyzed IRB Data";
			this.dgAnal.DataMember = "";
			this.dgAnal.Dock = System.Windows.Forms.DockStyle.Fill;
			this.dgAnal.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.dgAnal.Location = new System.Drawing.Point(0, 179);
			this.dgAnal.Name = "dgAnal";
			this.dgAnal.Size = new System.Drawing.Size(872, 189);
			this.dgAnal.TabIndex = 15;
			// 
			// splitter1
			// 
			this.splitter1.Dock = System.Windows.Forms.DockStyle.Top;
			this.splitter1.Location = new System.Drawing.Point(0, 176);
			this.splitter1.Name = "splitter1";
			this.splitter1.Size = new System.Drawing.Size(872, 3);
			this.splitter1.TabIndex = 3;
			this.splitter1.TabStop = false;
			// 
			// btnPrint
			// 
			this.btnPrint.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
			this.btnPrint.Enabled = false;
			this.btnPrint.Location = new System.Drawing.Point(136, 496);
			this.btnPrint.Name = "btnPrint";
			this.btnPrint.Size = new System.Drawing.Size(136, 23);
			this.btnPrint.TabIndex = 16;
			this.btnPrint.Text = "Print Analyzed Data";
			this.btnPrint.Click += new System.EventHandler(this.btnPrint_Click);
			// 
			// btnUpdate
			// 
			this.btnUpdate.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
			this.btnUpdate.Enabled = false;
			this.btnUpdate.Location = new System.Drawing.Point(600, 496);
			this.btnUpdate.Name = "btnUpdate";
			this.btnUpdate.Size = new System.Drawing.Size(160, 23);
			this.btnUpdate.TabIndex = 21;
			this.btnUpdate.Text = "Update Protocol Table";
			this.btnUpdate.Click += new System.EventHandler(this.btnUpdate_Click);
			// 
			// btnRemove
			// 
			this.btnRemove.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
			this.btnRemove.Enabled = false;
			this.btnRemove.Location = new System.Drawing.Point(280, 496);
			this.btnRemove.Name = "btnRemove";
			this.btnRemove.Size = new System.Drawing.Size(168, 23);
			this.btnRemove.TabIndex = 20;
			this.btnRemove.Text = "Remove Selected Row(s)";
			this.btnRemove.Click += new System.EventHandler(this.btnRemove_Click);
			// 
			// btnExit
			// 
			this.btnExit.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
			this.btnExit.Location = new System.Drawing.Point(792, 496);
			this.btnExit.Name = "btnExit";
			this.btnExit.Size = new System.Drawing.Size(56, 23);
			this.btnExit.TabIndex = 22;
			this.btnExit.Text = "Exit";
			this.btnExit.Click += new System.EventHandler(this.btnExit_Click);
			// 
			// btnRemoveExceptions
			// 
			this.btnRemoveExceptions.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
			this.btnRemoveExceptions.Enabled = false;
			this.btnRemoveExceptions.Location = new System.Drawing.Point(456, 496);
			this.btnRemoveExceptions.Name = "btnRemoveExceptions";
			this.btnRemoveExceptions.Size = new System.Drawing.Size(136, 23);
			this.btnRemoveExceptions.TabIndex = 23;
			this.btnRemoveExceptions.Text = "Remove Exceptions";
			this.btnRemoveExceptions.Click += new System.EventHandler(this.btnRemoveExceptions_Click);
			// 
			// FormIRBData
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 15);
			this.ClientSize = new System.Drawing.Size(880, 526);
			this.Controls.Add(this.btnRemoveExceptions);
			this.Controls.Add(this.btnExit);
			this.Controls.Add(this.btnUpdate);
			this.Controls.Add(this.btnRemove);
			this.Controls.Add(this.btnPrint);
			this.Controls.Add(this.panel1);
			this.Controls.Add(this.btnAnalyze);
			this.Controls.Add(this.gbxCriteria);
			this.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
			this.Name = "FormIRBData";
			this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
			this.Text = "Retrieve IRB Data";
			this.WindowState = System.Windows.Forms.FormWindowState.Maximized;
			this.gbxCriteria.ResumeLayout(false);
			this.groupBox1.ResumeLayout(false);
			((System.ComponentModel.ISupportInitialize)(this.EndDate.Calendar)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.EndDate)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.BeginDate.Calendar)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.BeginDate)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.dg)).EndInit();
			this.panel1.ResumeLayout(false);
			((System.ComponentModel.ISupportInitialize)(this.dgAnal)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion

		private void btnAnalyze_Click(object sender, System.EventArgs e)
		{
			this.btnPrint.Enabled = false;
			btnUpdate.Enabled = false;
			btnRemove.Enabled = false;
			btnRemoveExceptions.Enabled = false;
			dgAnal.CaptionText = "Analyzed IRB Data";

			//this clears data, not column schema
			_dtAnalyzed.Clear();

			DataTable dt = (DataTable)dg.DataSource;
			DataRow drAnalyzed;
			if (dt.Rows.Count == 0)
				return;

			dgAnal.DataSource = null;

			Cursor.Current = Cursors.WaitCursor;
			this.btnPrint.Enabled = true;
			foreach (DataRow drIrb in dt.Rows)
			{
				//create new analyzed row
				drAnalyzed = _dtAnalyzed.NewRow();

				AnalyzeRow(drIrb, drAnalyzed);
				try
				{
					_dtAnalyzed.Rows.Add(drAnalyzed);
				}
				catch (System.Data.ConstraintException ex)
				{
					Console.WriteLine(ex.Message);
					this._dupeCount++;
					drAnalyzed["ProtocolNumber"] = drAnalyzed["ProtocolNumber"] + "*" + this._dupeCount + "*";
					drAnalyzed["Status"] = "Exception";
					drAnalyzed["Message"] = "A record with this protocol number has already been analyzed.";
					_dtAnalyzed.Rows.Add(drAnalyzed);
				}

			}

			DataView dv = new DataView(this._dtAnalyzed);
			dgAnal.DataSource = dv;

			this.btnPrint.Enabled = true; ;
			btnUpdate.Enabled = true;
			btnRemove.Enabled = true;
			btnRemoveExceptions.Enabled = true;
		}

		private void AnalyzeRow(DataRow drIrb, DataRow drAnalyzed)
		{
			StringBuilder sb = new StringBuilder();
			StringBuilder message = new StringBuilder();
			ReviewType reviewType = ReviewType.UnAssigned;

			string status = drIrb["STATUS_CD"].ToString();
			string protoNumber = drIrb["PROTOCOL_NUM"].ToString();
			string reviewCode = drIrb["REVIEW_CD"].ToString();
			string previousNumber = "";
			string nextNumber = "";

			if (drIrb["PREV_PROTOCOL_NUM"] != System.DBNull.Value)
				previousNumber = drIrb["PREV_PROTOCOL_NUM"].ToString();

			if (drIrb["NEXT_PROTOCOL_NUM"] != System.DBNull.Value)
				nextNumber = drIrb["NEXT_PROTOCOL_NUM"].ToString();

			drAnalyzed["ProtocolNumber"] = protoNumber;
			drAnalyzed["IRBApproval"] = (DateTime)drIrb["APPROVAL_DT"];
			drAnalyzed["ReviewCode"] = reviewCode;
			bool isRewrite = false;
			if (protoNumber.EndsWith("R"))
				isRewrite = true;

			//make sure status code is one of these - we only handle these codes
			if (!(status == "A" || status == "D" || status == "T" || status == "W"))
			{
				drAnalyzed["ReviewType"] = "***";
				drAnalyzed["Status"] = "Exception";
				drAnalyzed["Message"] = "Status code - not of processing type.";
				drAnalyzed["ComparingData"] = "Status Code: " + status;
				return;
			}

			//assign terminiate if it's one of these codes
			if (status == "D" || status == "T" || status == "W")
				reviewType = ReviewType.Terminate;

			//Unassigned is the default - so if its still this then
			//lets assign it
			if (reviewType == ReviewType.UnAssigned)
			{
				//make sure review code is one of these
				if (reviewCode == "I")
				{
					reviewType = ReviewType.Initial;
				}
				else
				{
					if (IsCodeRenewal(reviewCode))//this makes sure the code begins with A and ends with a number
						reviewType = ReviewType.Continue;
					else //code is not known
					{
						drAnalyzed["ReviewType"] = "Unknown";
						drAnalyzed["Status"] = "Exception";
						drAnalyzed["Message"] = "Review code field was not recognized.";
						drAnalyzed["ComparingData"] = "Review code: " + reviewCode + " - Irb approval date: " + ((DateTime)drIrb["APPROVAL_DT"]).ToShortDateString();
						return;
					}
				}
			}
			drAnalyzed["ReviewType"] = reviewType.ToString();

			bool bExists = false;
			bool bNextNumExists = false;

			//see if the protocol exists in the table
			string sql = "SELECT ID,IrbApproval,IrbRenewal,CrcTerminate FROM Protocol " +
				" WHERE ProtocolNumber='" + protoNumber + "'";

			//SqlConnection conn = LoginForm.GetSqlConnection();
			IDataReader reader = null;
			try
			{
				reader = DataAccess.GetDataReader(sql);

				bExists = reader.Read();

				if (bExists)
					drAnalyzed["ID"] = reader.GetInt32(0).ToString();
				else
					drAnalyzed["ID"] = "***";

				//protocol number exists in table
				switch (reviewType)
				{
					case ReviewType.Initial:
						AnalyzeInitial(bExists, isRewrite, reader, drIrb, drAnalyzed);
						break;
					case ReviewType.Continue:
						AnalyzeContinue(bExists, isRewrite, reader, drIrb, drAnalyzed);
						break;
					case ReviewType.Terminate:
						if (nextNumber.Length > 0)
						{
							bNextNumExists = NextProtoNumberExists(nextNumber);
							if (bExists)
							{
								drAnalyzed["Status"] = "Exception";
								drAnalyzed["Message"] = "Protocol exists.  IRB has assigned a Next Number: " + nextNumber + " - this protcol should be a rewrite.";
								drAnalyzed["ComparingData"] = "Irb approval date: " + ((DateTime)drIrb["APPROVAL_DT"]).ToShortDateString();
							}
							else
							{
								drAnalyzed["Status"] = "Exception";
								message.Append("Protocol not found, however IRB has assigned a Next Number: " + nextNumber);
								message.Append(Environment.NewLine);
								if (bNextNumExists)
									message.Append("the next number was found.");
								else
									message.Append("the next number was not found.");
								drAnalyzed["Message"] = message.ToString();
								drAnalyzed["ComparingData"] = "Irb approval date: " + ((DateTime)drIrb["APPROVAL_DT"]).ToShortDateString();
							}

							return;
						}
						AnalyzeTerminate(bExists, reader, drIrb, drAnalyzed);
						break;
				}
			}
			catch (Exception ex)
			{
				sb.Append("Could not retrieve protocol info!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Retrieve Protocol Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return;
			}
			finally
			{
				if (reader != null)
				{
					if (!reader.IsClosed)
						reader.Close();
				}
				//if(conn != null)
				//    conn.Dispose();
			}
		}

		private bool NextProtoNumberExists(string number)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT ID FROM Protocol WHERE ProtocolNumber='" + number + "'";

			//SqlConnection conn = LoginForm.GetSqlConnection();
			IDataReader reader = null;
			try
			{
				reader = DataAccess.GetDataReader(sql);
				return reader.Read();
			}
			catch (Exception ex)
			{
				sb.Append("Could not retrieve protocol info!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Retrieve Protocol Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return false;
			}
			finally
			{
				if (reader != null)
				{
					if (!reader.IsClosed)
						reader.Close();
				}
				//if(conn != null)
				//    conn.Dispose();
			}
		}

		private bool IsCodeRenewal(string code)
		{
			if (!code.StartsWith("A"))
				return false;
			string end = code.Substring(1);

			try
			{
				int n = Convert.ToInt32(end);
				return true;
			}
			catch (Exception ex)
			{
				Console.WriteLine(ex.Message);
				return false;
			}
		}

		private void AnalyzeTerminate(bool bExists, IDataReader reader, DataRow drIrb, DataRow drAnalyzed)
		{
			if (!bExists)
			{
				drAnalyzed["Status"] = "Exception";
				drAnalyzed["Message"] = "This protocol was not found in the existing table.";
				return;
			}

			//TODO - check for the IRB.notes for terminate/completed/term... 

			string crcTerminate = "";
			//check completed date
			int ord = reader.GetOrdinal("CrcTerminate");
			if (reader.IsDBNull(ord))
			{
				drAnalyzed["Status"] = "Update T";
				drAnalyzed["Message"] = "Update CrcTerminate with IRB  approval date.";
			}
			else
			{
				crcTerminate = reader.GetDateTime(ord).ToShortDateString();
				drAnalyzed["Status"] = "Exception";
				drAnalyzed["Message"] = "CrcTerminate date is already entered.";
			}
			drAnalyzed["ComparingData"] = GetComparingTerminateData(crcTerminate, drIrb);
		}

		private string GetComparingTerminateData(string terminateDate, DataRow drIrb)
		{
			StringBuilder sb = new StringBuilder();
			string irbApproval = ((DateTime)drIrb["APPROVAL_DT"]).ToShortDateString();

			if (terminateDate.Length == 0)
				sb.Append("Existing CrcTerminate date is not entered.");
			else
				sb.Append("Existing CrcTerminate date: " + terminateDate);

			sb.Append(Environment.NewLine);
			sb.Append("IRB approval date: " + irbApproval);
			return sb.ToString();
		}

		private void AnalyzeContinue(bool bExists, bool isRewrite, IDataReader reader, DataRow drIrb, DataRow drAnalyzed)
		{
			string previousNumber;
			DataRow drExist = null;
			StringBuilder sb = new StringBuilder();
			if (!bExists)
			{
				if (isRewrite)
				{
					previousNumber = drIrb["PREV_PROTOCOL_NUM"].ToString();

					if (previousNumber.Length > 0)
					{
						if (this.FindExistingProtocol(ref drExist, previousNumber))
						{
							drAnalyzed["ID"] = drExist["ID"];
							drAnalyzed["Status"] = "Rewrite";
							sb.Append("Found existing protocol number with IRB previous number.");
							drAnalyzed["Message"] = sb.ToString();
							sb = new StringBuilder();
							sb.Append("IRB Previous Number: " + previousNumber);
							sb.Append(Environment.NewLine);
							sb.Append(this.GetComparingData(drExist, drIrb));
							drAnalyzed["ComparingData"] = sb.ToString();
						}
						else
						{
							drAnalyzed["Status"] = "Exception";
							sb.Append("Could not find existing protocol and");
							sb.Append(Environment.NewLine);
							sb.Append("could not find protocol with previous protocol number.");
							drAnalyzed["Message"] = sb.ToString();
							sb = new StringBuilder();
							sb.Append("IRB approval date: " + ((DateTime)drIrb["APPROVAL_DT"]));
							sb.Append(Environment.NewLine);
							sb.Append("IRB Previous Protocol Number: " + previousNumber);
							drAnalyzed["ComparingData"] = sb.ToString();
						}
					}
					else
					{
						drAnalyzed["Status"] = "Exception";
						sb.Append("Could not find existing protocol and");
						sb.Append(Environment.NewLine);
						sb.Append("and there is no IRB Previous Protocol Number.");
						drAnalyzed["Message"] = sb.ToString();
						drAnalyzed["ComparingData"] = "Irb approval date: " + ((DateTime)drIrb["APPROVAL_DT"]);
					}
				}
				else
				{
					drAnalyzed["Status"] = "Exception";
					drAnalyzed["Message"] = "Could not find existing protocol.";
					drAnalyzed["ComparingData"] = "Irb approval date: " + ((DateTime)drIrb["APPROVAL_DT"]).ToShortDateString();
				}
				return;
			}

			DateTime dtIrbAprv = (DateTime)drIrb["APPROVAL_DT"];
			DateTime dtExtRenw = new DateTime(0);
			TimeSpan ts;

			int ord = reader.GetOrdinal("IrbRenewal");
			if (!reader.IsDBNull(ord))
			{
				dtExtRenw = reader.GetDateTime(ord);
				ts = dtIrbAprv.Subtract(dtExtRenw);

				if (ts.Days == 0)
				{
					drAnalyzed["Status"] = "Exception";
					sb.Append("Renewal date for Existing Protocol is equal");
					sb.Append(Environment.NewLine);
					sb.Append("to the IRB approval date");
					sb.Append(Environment.NewLine);
					sb.Append("It looks like it's been previously updated.");
					drAnalyzed["Message"] = sb.ToString();
				}
				else if (ts.Days < 0)
				{
					drAnalyzed["Status"] = "Exception";
					sb.Append("Renewal date for Existing Protocol is greater");
					sb.Append(Environment.NewLine);
					sb.Append("than the irb approval date.");
					drAnalyzed["Message"] = sb.ToString();
				}
				else
				{
					drAnalyzed["Status"] = "Update R";
					sb.Append("Update existing Renewal date with the");
					sb.Append(Environment.NewLine);
					sb.Append("irb approval date.");
					drAnalyzed["Message"] = sb.ToString();
				}
			}
			else
			{
				drAnalyzed["Status"] = "Update R";
				sb.Append("Update existing Renewal date with the");
				sb.Append(Environment.NewLine);
				sb.Append("irb approval date.");
				drAnalyzed["Message"] = sb.ToString();
			}
			drAnalyzed["ComparingData"] = this.GetComparingData(reader, drIrb);
		}

		private void AnalyzeInitial(bool bExists, bool isRewrite, IDataReader reader, DataRow drIrb, DataRow drAnalyzed)
		{
			StringBuilder message = new StringBuilder();

			if (bExists)
			{
				//if protocol exist's then it was probably entered already
				//check the approval date to see if it matches				
				if (isRewrite)
					AnalyzeRenewalDates(reader, drIrb, drAnalyzed);
				else
					AnalyzeApprovalDates(reader, drIrb, drAnalyzed);
			}
			else
			{
				if (isRewrite)
				{
					AnalyzeRewriteInitial(drIrb, drAnalyzed);
				}
				else
				{
					//if it doesn't exist then add it
					message.Append("Existing protocol not found.");
					message.Append(Environment.NewLine);
					message.Append("Add new protocol.");

					drAnalyzed["Status"] = "New";
					drAnalyzed["Message"] = message.ToString();
					drAnalyzed["ComparingData"] = "Irb approval date: " + ((DateTime)drIrb["APPROVAL_DT"]).ToShortDateString();
				}
			}
		}

		private void AnalyzeRewriteInitial(DataRow drIrb, DataRow drAnalyzed)
		{
			StringBuilder sb = new StringBuilder();
			string previousNumber = drIrb["PREV_PROTOCOL_NUM"].ToString();

			//we already searched existing db for irb number to see if it already has been entered
			//and could not find it
			if (previousNumber.Length == 0)
			{
				drAnalyzed["Status"] = "Exception";
				sb.Append("Existing Protocol was not found and");
				sb.Append(Environment.NewLine);
				sb.Append("there is no IRB previous number.");
				drAnalyzed["Message"] = sb.ToString();
				drAnalyzed["ComparingData"] = "";
				return;
			}

			//search existing db for previous number
			string sql = "SELECT ID,IrbApproval,IrbRenewal FROM Protocol " +
				" WHERE ProtocolNumber='" + previousNumber + "'";

			DataRow drExist = null;
			if (this.FindExistingProtocol(ref drExist, previousNumber))
			{
				drAnalyzed["ID"] = drExist["ID"];
				drAnalyzed["Status"] = "Rewrite";
				sb.Append("Found existing protocol number with the IRB previous number.");
				drAnalyzed["Message"] = sb.ToString();
				sb = new StringBuilder();
				sb.Append("IRB Previous Number: " + previousNumber);
				sb.Append(Environment.NewLine);
				sb.Append(this.GetComparingData(drExist, drIrb));
				drAnalyzed["ComparingData"] = sb.ToString();
			}
			else
			{
				drAnalyzed["Status"] = "Exception";
				sb.Append("Could not find existing protocol and");
				sb.Append(Environment.NewLine);
				sb.Append("could not find protocol with IRB previous number.");
				drAnalyzed["Message"] = sb.ToString();
				sb = new StringBuilder();
				sb.Append("IRB approval date: " + ((DateTime)drIrb["APPROVAL_DT"]));
				sb.Append(Environment.NewLine);
				sb.Append("IRB Previous Protocol Number: " + previousNumber);
				drAnalyzed["ComparingData"] = sb.ToString();
			}
		}

		private string GetComparingData(DataRow drExist, DataRow drIrb)
		{
			StringBuilder sb = new StringBuilder();
			string existApproval = "";
			string existRenewal = "";
			string irbApproval = ((DateTime)drIrb["APPROVAL_DT"]).ToShortDateString();

			if (drExist["IrbApproval"] == System.DBNull.Value)
				existApproval = "Not Entered";
			else
				existApproval = ((DateTime)drExist["IrbApproval"]).ToShortDateString();

			if (drExist["IrbRenewal"] == System.DBNull.Value)
				existRenewal = "Not Entered";
			else
				existRenewal = ((DateTime)drExist["IrbRenewal"]).ToShortDateString();


			sb.Append("Existing approval date: " + existApproval);
			sb.Append(Environment.NewLine);
			sb.Append("Existing renewal date: " + existRenewal);
			sb.Append(Environment.NewLine);
			sb.Append("IRB approval date: " + irbApproval);

			return sb.ToString();
		}

		private string GetComparingData(IDataReader reader, DataRow drIrb)
		{
			StringBuilder sb = new StringBuilder();
			string existApproval = "";
			string existRenewal = "";
			string irbApproval = ((DateTime)drIrb["APPROVAL_DT"]).ToShortDateString();

			int ord = reader.GetOrdinal("IrbApproval");
			if (reader.IsDBNull(ord))
				existApproval = "Not Entered";
			else
				existApproval = reader.GetDateTime(ord).ToShortDateString();

			ord = reader.GetOrdinal("IrbRenewal");
			if (reader.IsDBNull(ord))
				existRenewal = "Not Entered";
			else
				existRenewal = reader.GetDateTime(ord).ToShortDateString();

			sb.Append("Existing approval date: " + existApproval);
			sb.Append(Environment.NewLine);
			sb.Append("Existing renewal date: " + existRenewal);
			sb.Append(Environment.NewLine);
			sb.Append("IRB approval date: " + irbApproval);

			return sb.ToString();
		}

		private void AnalyzeRenewalDates(IDataReader reader, DataRow drIrb, DataRow drAnalyzed)
		{
			StringBuilder message = new StringBuilder();
			DateTime dtIrb = (DateTime)drIrb["APPROVAL_DT"];
			DateTime dtExistRenewal = new DateTime(0);
			string sExistApproval = "";

			//this is just for display
			int ord = reader.GetOrdinal("IrbApproval");
			if (reader.IsDBNull(ord))
				sExistApproval = "Not Entered.";
			else
				sExistApproval = reader.GetDateTime(ord).ToShortDateString();

			//now compare renewal
			ord = reader.GetOrdinal("IrbRenewal");
			if (reader.IsDBNull(ord))
			{
				drAnalyzed["Status"] = "Update R";
				message.Append("Protocol already exists in the database.");
				message.Append(Environment.NewLine);
				message.Append("The renewal date has not been entered.");
				message.Append(Environment.NewLine);
				message.Append("Renewal date will be updated.");
				drAnalyzed["Message"] = message.ToString();
			}
			else
			{
				drAnalyzed["Status"] = "Exception";
				message.Append("Protocol already exists in the database.");
				message.Append(Environment.NewLine);
				dtExistRenewal = reader.GetDateTime(ord);
				TimeSpan ts = dtIrb.Subtract(dtExistRenewal);
				if (ts.Days == 0)
				{
					message.Append("Existing renewal date equals IRB approval date.");
					message.Append(Environment.NewLine);
					message.Append("It looks like it's been previously entered.");
				}
				else
					message.Append("Existing renewal date does not equal irb approval date.");
				drAnalyzed["Message"] = message.ToString();
			}
			drAnalyzed["ComparingData"] = this.GetComparingData(reader, drIrb);
		}

		private void AnalyzeApprovalDates(IDataReader reader, DataRow drIrb, DataRow drAnalyzed)
		{
			//only called by Initial Review Types
			StringBuilder message = new StringBuilder();
			if (drIrb["APPROVAL_DT"] == System.DBNull.Value)
			{
				drAnalyzed["Status"] = "Exception";
				drAnalyzed["Message"] = "There is no IRP approval date.";
				return;
			}
			DateTime dtIrb = (DateTime)drIrb["APPROVAL_DT"];
			DateTime dtExist = new DateTime(0);

			int ord = reader.GetOrdinal("IrbApproval");
			if (reader.IsDBNull(ord))
			{
				drAnalyzed["Status"] = "Update A";
				message.Append("Protocol already exists in the database.");
				message.Append(Environment.NewLine);
				message.Append("The approval date has not been entered.");
				message.Append(Environment.NewLine);
				message.Append("Approval date will be updated.");
				drAnalyzed["Message"] = message.ToString();
			}
			else
			{
				drAnalyzed["Status"] = "Exception";
				message.Append("Protocol already exists in the database.");
				message.Append(Environment.NewLine);
				dtExist = reader.GetDateTime(ord);
				TimeSpan ts = dtIrb.Subtract(dtExist);
				if (ts.Days == 0)
				{
					message.Append("Existing approval date equals IRB approval date.");
					message.Append(Environment.NewLine);
					message.Append("It looks like it's been previously entered.");
				}
				else
					message.Append("Existing approval date does not equal irb approval date.");
				drAnalyzed["Message"] = message.ToString();
			}
			drAnalyzed["ComparingData"] = this.GetComparingData(reader, drIrb);
		}

		private void btnGetData_Click(object sender, System.EventArgs e)
		{
			//initialize
			this.btnAnalyze.Enabled = false;
			btnUpdate.Enabled = false;
			btnRemove.Enabled = false;
			btnRemoveExceptions.Enabled = false;
			btnPrint.Text = "Print Analyzed Data";
			this.btnPrint.Enabled = false;
			string criteria = "";

			CriteriaType critType = CriteriaType.ReturnAll;
			IRBWebService.SortOrder order = IRBWebService.SortOrder.ApprovalDate;

			dgAnal.DataSource = null;
			dg.DataSource = null;

			if (rdoSortNumber.Checked)
				order = IRBWebService.SortOrder.ProtocolNumber;

			if (rdoDates.Checked)
			{
				critType = CriteriaType.ApprovalDate;
				if (!BeginDate.IsNullDate)
				{
					criteria = BeginDate.Value.ToShortDateString();
					criteria += "," + EndDate.Value.ToShortDateString();
				}
			}
			else if (rdoNumber.Checked)
			{
				critType = CriteriaType.ProtocolNumber;
				criteria = Number.Text;
			}

			Cursor.Current = Cursors.WaitCursor;
			try
			{
				AdminService ads = new AdminService();
				DataSet ds = ads.GetNewProtocols(critType, order, criteria);
				if (ds.Tables.Count > 0)
				{

					dg.DataSource = ds.Tables[0];
					if (ds.Tables[0].Rows.Count > 0)
						this.btnAnalyze.Enabled = true;
					else
					{
						MessageBox.Show("Did not return any rows!");
					}
				}

			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message);
			}
		}

		private bool FindExistingProtocol(ref DataRow dr, string criteria)
		{
			//search existing db for previous number
			StringBuilder sb = new StringBuilder();
			string sql = "SELECT ID,IrbApproval,IrbRenewal FROM Protocol " +
				" WHERE ProtocolNumber='" + criteria + "'";

			DataTable dt = new DataTable();
			//SqlConnection conn = LoginForm.GetSqlConnection();
			try
			{
				DataSet ds = DataAccess.GetDataSet(sql);
				dt = ds.Tables[0];
				if (dt.Rows.Count > 0)
					dr = dt.Rows[0];
				else
				{
					dr = null;
					return false;
				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Could not execute query for finding a protocol!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Find Protocol Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return false;
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();
			}

		}

		private void btnRemoveExceptions_Click(object sender, System.EventArgs e)
		{
			//find the sort column if any
			DataView dv = (DataView)dgAnal.DataSource;
			string sortCol = dv.Sort;

			DataRow dr = null;
			bool notDone = true;
			while (notDone)
			{
				bool didDelete = false;
				for (int i = 0; i < this._dtAnalyzed.Rows.Count; i++)
				{
					dr = this._dtAnalyzed.Rows[i];
					if (dr["Status"].ToString() == "Exception")
					{
						didDelete = true;
						dr.Delete();
					}
					if (didDelete)
						break;
				}

				if (!didDelete)
					notDone = false;
			}

			this._dtAnalyzed.AcceptChanges();

			this.dgAnal.DataSource = null;
			dv = new DataView(this._dtAnalyzed);
			dv.Sort = sortCol;
			this.dgAnal.DataSource = dv;
		}

		private void btnRemove_Click(object sender, System.EventArgs e)
		{
			int row = dgAnal.CurrentRowIndex;
			if (row == -1)
			{
				MessageBox.Show("Select a row(s).");
				return;
			}

			//delete multiple rows
			ArrayList al = new ArrayList();
			DataView dv = (DataView)dgAnal.DataSource;
			//get the sort column
			string sortCol = dv.Sort;

			for (int i = 0; i < dv.Count; ++i)
			{
				if (dgAnal.IsSelected(i))
					al.Add(i);
			}

			for (int i = al.Count - 1; i > -1; i--)
			{
				int index = (int)al[i];
				DataRow dr = dv[index].Row;
				string protoNumb = dr[1].ToString();
				Console.WriteLine("protocol number: " + protoNumb);

				//find the proto in the dataTable and remove it
				DataRow dr2 = this._dtAnalyzed.Rows.Find(protoNumb);
				if (dr2 != null)
				{
					this._dtAnalyzed.Rows.Remove(dr2);
				}
			}

			//refresh the grid data source
			this.dgAnal.DataSource = null;
			dv = new DataView(this._dtAnalyzed);
			if (dv.Sort.Length > 0)
				dv.Sort = sortCol;
			this.dgAnal.DataSource = dv;
		}

		private void btnUpdate_Click(object sender, System.EventArgs e)
		{
			//clear the results table
			this._dtResults.Clear();
			this.btnUpdate.Enabled = false;
			this.btnPrint.Enabled = false;
			this.btnRemove.Enabled = false;
			btnRemoveExceptions.Enabled = false;
			this.btnAnalyze.Enabled = false;


			StringBuilder sb = new StringBuilder();
			foreach (DataRow dr in _dtAnalyzed.Rows)
			{
				string status = dr["Status"].ToString();
				string protoNumber;
				DateTime dtApproval;

				if (status == "Exception")
					continue;

				protoNumber = dr["ProtocolNumber"].ToString();
				dtApproval = (DateTime)dr["IRBApproval"];
				DataRow drIrb = GetIrbDataRow(protoNumber, dtApproval);

				if (drIrb == null)
				{
					sb.Append("Could not update protocol : " + protoNumber);
					sb.Append(Environment.NewLine);
					sb.Append("GetIrbDataRow failed");
					MessageBox.Show(this, sb.ToString(), "Get IRB Row Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
					continue;
				}

				DataRow drResults = this._dtResults.NewRow();
				drResults["IRBApproval"] = dr["IRBApproval"];
				int id = 0;
				switch (status)
				{
					case "Update R":
						id = Convert.ToInt32(dr["ID"].ToString());
						UpdateProtocol(drIrb, "IrbRenewal", id, drResults);
						break;
					case "Update A":
						id = Convert.ToInt32(dr["ID"].ToString());
						UpdateProtocol(drIrb, "IrbApproval", id, drResults);
						break;
					case "Update T":
						id = Convert.ToInt32(dr["ID"].ToString());
						UpdateProtocol(drIrb, "CrcTerminate", id, drResults);
						break;
					case "Rewrite":
						id = Convert.ToInt32(dr["ID"].ToString());
						DoRewrite(drIrb, id, drResults);
						break;
					case "New":
						int newProtoID = AddNewProtocol(drIrb, drResults);
						if (newProtoID == 0)
							return;
						break;
				}
				this._dtResults.Rows.Add(drResults);
				AddJournalEntry(drResults);
			}
			if (this._dtResults.Rows.Count > 0)
			{
				dgAnal.CaptionText = "Updated Results";
				this.dgAnal.DataSource = this._dtResults;
				btnPrint.Text = "Print Results Data";
				this.btnPrint.Enabled = true;
				this.btnRemove.Enabled = true;
				btnRemoveExceptions.Enabled = true;
			}
		}

		private void DoRewrite(DataRow drIrb, int id, DataRow drResults)
		{

			StringBuilder sb = new StringBuilder();
			string protoNumber = drIrb["PROTOCOL_NUM"].ToString();
			string previousNumber = drIrb["PREV_PROTOCOL_NUM"].ToString();
			DateTime dtApproval = (DateTime)drIrb["APPROVAL_DT"];

			drResults["ID"] = id.ToString();
			drResults["IRBApproval"] = dtApproval;
			drResults["ProtocolNumber"] = protoNumber;
			drResults["Action"] = "Update Rewrite";


			string sql = "UPDATE Protocol Set ProtocolNumber=@ProtocolNumber, " +
				"IrbRenewal=@IrbRenewal" +
				" WHERE ID=" + id;

			SqlParameter[] aParams = new SqlParameter[2];

			aParams[0] = new SqlParameter("@IrbRenewal", SqlDbType.DateTime, 0, "IrbRenewal");
			aParams[0].Value = dtApproval;

			aParams[1] = new SqlParameter("@ProtocolNumber", SqlDbType.NVarChar, 50, "ProtocolNumber");
			aParams[1].Value = protoNumber;

			Database db = DatabaseFactory.CreateDatabase();
			using (DbConnection conn = db.CreateConnection())
			{
				try
				{
					conn.Open();
					DbTransaction trans = conn.BeginTransaction();
					if (!AddPreviousNumber(db, trans, previousNumber, id, drResults))
					{
						trans.Rollback();
						return;
					}

					DbCommand cmd = db.GetSqlStringCommand(sql);
					DataAccess.AddParamArrayToDbCommand(cmd, aParams);
					int rslt = db.ExecuteNonQuery(cmd, trans);
					if (rslt < 1)
					{
						sb.Append("Could not update rewrite, protocol number " + protoNumber);
						sb.Append(Environment.NewLine);
						sb.Append("ExecuteNonQuery returned 0");
						MessageBox.Show(this, sb.ToString(), "Update Protocol Rewrite Failed", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
						drResults["Result"] = "Failed";
						drResults["Message"] = sb.ToString();
						trans.Rollback();
						return;
					}

					trans.Commit();
					sb.Append("Update rewrite executed, protocol number " + protoNumber);
					sb.Append(Environment.NewLine);
					sb.Append("Previous protocol number " + previousNumber);
					MessageBox.Show(this, sb.ToString(), "Update Protocol Rewrite Failed", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
					drResults["Result"] = "Executed";
					drResults["Message"] = sb.ToString();
				}
				catch (Exception ex)
				{
					sb.Append("Could not update 'rewrite' protocol number " + protoNumber);
					sb.Append(Environment.NewLine);
					sb.Append("Error message: " + ex.Message);
					//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
					MessageBox.Show(this, sb.ToString(), "Update Rewrite Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
					drResults["Result"] = "Error";
					drResults["Message"] = sb.ToString();
				}
				finally
				{
					if (conn != null)
						conn.Dispose();
				}
			}
		}

		private bool AddPreviousNumber(Database db, DbTransaction trans, string previousNumber, int id, DataRow drResults)
		{
			StringBuilder sb = new StringBuilder();
			string sql = "INSERT INTO ProtocolPreviousNumber (ProtocolID,PreviousNumber) " +
				" VALUES(@ProtocolID,@PreviousNumber)";

			SqlParameter[] aParams = new SqlParameter[2];

			aParams[0] = new SqlParameter("@ProtocolID", SqlDbType.Int, 0, "ProtocolID");
			aParams[0].Value = id;

			aParams[1] = new SqlParameter("@PreviousNumber", SqlDbType.NVarChar, 50, "PreviousNumber");
			aParams[1].Value = previousNumber;

			try
			{
				DbCommand cmd = db.GetSqlStringCommand(sql);
				DataAccess.AddParamArrayToDbCommand(cmd, aParams);
				int rslt = db.ExecuteNonQuery(cmd, trans);
				if (rslt < 1)
				{
					sb.Append("Could not update previous protocol number " + previousNumber);
					sb.Append(Environment.NewLine);
					sb.Append("ExecuteNonQuery returned 0");
					MessageBox.Show(this, sb.ToString(), "Update Previous Protocol Number Failed", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
					drResults["Result"] = "Failed";
					drResults["Message"] = sb.ToString();
					return false;
				}
				return true;
			}
			catch (Exception ex)
			{
				sb.Append("Could not update previous protocol number " + previousNumber);
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Update Previous Protocol Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				drResults["Result"] = "Error";
				drResults["Message"] = sb.ToString();
				return false;
			}
		}

		private void AddJournalEntry(DataRow drResults)
		{
			StringBuilder sb = new StringBuilder();
			string protoNumber = "";
			string sql = "INSERT INTO IRBImportJournal (ApprovalDate,ProtocolID,ProtocolNumber,UpdateType, " +
				" UpdateResult,Message) VALUES(@ApprovalDate,@ProtocolID,@ProtocolNumber,@UpdateType, " +
				" @UpdateResult,@Message)";

			SqlConnection conn = DataAccess.GetSqlConnection();
			SqlCommand cmd = new SqlCommand(sql, conn);

			SqlParameter param = new SqlParameter("@ApprovalDate", SqlDbType.DateTime, 0, "ApprovalDate");
			param.Value = (DateTime)drResults["IRBApproval"];
			cmd.Parameters.Add(param);

			param = new SqlParameter("@ProtocolID", SqlDbType.Int, 0, "ProtocolID");
			param.Value = Convert.ToInt32(drResults["ID"].ToString());
			cmd.Parameters.Add(param);

			param = new SqlParameter("@ProtocolNumber", SqlDbType.NVarChar, 50, "ProtocolNumber");
			protoNumber = drResults["ProtocolNumber"].ToString();
			param.Value = protoNumber;
			cmd.Parameters.Add(param);

			param = new SqlParameter("@UpdateType", SqlDbType.NVarChar, 50, "UpdateType");
			param.Value = drResults["Action"].ToString();
			cmd.Parameters.Add(param);

			param = new SqlParameter("@UpdateResult", SqlDbType.NVarChar, 50, "UpdateResult");
			param.Value = drResults["Result"].ToString();
			cmd.Parameters.Add(param);

			param = new SqlParameter("@Message", SqlDbType.NVarChar, 500, "Message");
			param.Value = drResults["Message"].ToString();
			cmd.Parameters.Add(param);

			try
			{
				conn.Open();
				int rslt = cmd.ExecuteNonQuery();

				if (rslt != 1)
				{
					sb.Append("Could not add journal entry for " + protoNumber);
					sb.Append(Environment.NewLine);
					sb.Append(Shared.Constants.NotifySupport);
					MessageBox.Show(this, sb.ToString(), "Add Journal Entry Failed", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
				}
			}
			catch (Exception ex)
			{
				sb.Append("Could not add journal entry for " + protoNumber);
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Add Journal Entry Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}

		private void AddProtoInvestigator(DataRow drIrb, int newProtoID)
		{
			StringBuilder sb = new StringBuilder();
			string lastName = drIrb["LAST_NAME"].ToString();
			string firstName = drIrb["FIRST_NAME"].ToString();
			string degrees = drIrb["SUFFIX_CD"].ToString();

			string sql = "SELECT ID FROM Investigator " +
				" WHERE LastName ='" + lastName + "'" +
				" AND FirstName ='" + firstName + "'";

			//SqlConnection conn = LoginForm.GetSqlConnection();
			IDataReader reader = null;
			try
			{
				reader = DataAccess.GetDataReader(sql);
				bool b = reader.Read();

				if (!b)
					AddNewInvestigator();

			}
			catch (Exception ex)
			{
				sb.Append("Could not add investigator info for protocol id " + newProtoID + "!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Add Investigator Information Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return;
			}
			finally
			{
				if (reader != null)
				{
					if (!reader.IsClosed)
						reader.Close();
				}
				//if(conn != null)
				//    conn.Dispose();
			}

		}

		private void AddNewInvestigator()
		{
		}

		private int GetNextSpid()
		{
			StringBuilder sb = new StringBuilder();
			int spid = 0;

			//SqlConnection conn = LoginForm.GetSqlConnection();
			try
			{
				//See if the form is locked			
				if ((bool)DataAccess.GetScalarObject(CommandType.Text, "SELECT IsLocked FROM FormLock WHERE FormID=24"))
				{
					sb.Append("The protocol table is locked.  Someone else is adding a new protocol.");
					sb.Append(Environment.NewLine);
					sb.Append("Try again after they have saved the new protocol.");
					MessageBox.Show(this, sb.ToString(), "Table Lock", MessageBoxButtons.OK, MessageBoxIcon.Stop);
					return spid;
				}

				//lock the protocol
				int rslt = DataAccess.ExecuteNonQuery(CommandType.Text, "UPDATE FormLock SET IsLocked = 1 WHERE FormID=24");
				if (rslt != 1)
				{
					sb.Append("Could not lock protocol form.");
					sb.Append(Environment.NewLine);
					sb.Append(Shared.Constants.NotifySupportImmediate);
					MessageBox.Show(this, sb.ToString(), "Table Lock", MessageBoxButtons.OK, MessageBoxIcon.Stop);
					return spid;
				}
				//get the last spid number and increment it				
				spid = (int)DataAccess.GetScalarObject(CommandType.Text, "SELECT MAX(SPID) FROM Protocol");
				if (spid > 0)
					spid++;
				return spid;
			}
			catch (Exception ex)
			{
				sb.Append("Could not get next SPID number.");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Get Next SPID Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return spid;
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();
			}

		}

		private void UnlockForm()
		{
			StringBuilder sb = new StringBuilder();

			//SqlConnection conn = LoginForm.GetSqlConnection();
			try
			{
				//unlock the protocol 
				if (DataAccess.ExecuteNonQuery(CommandType.Text, "UPDATE FormLock SET IsLocked = 0 WHERE FormID=24") != 1)
				{
					sb.Append("Could not unlock protocol form.");
					sb.Append(Environment.NewLine);
					sb.Append(Shared.Constants.NotifySupportImmediate);
					MessageBox.Show(this, sb.ToString(), "Table Unlock", MessageBoxButtons.OK, MessageBoxIcon.Stop);
				}
				return;
			}
			catch (Exception ex)
			{
				sb.Append("Could not unlock protocol form.");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Unlock Protocol Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();
			}

		}

		private int AddNewProtocol(DataRow drIrb, DataRow drResults)
		{
			StringBuilder sb = new StringBuilder();
			string protoNumber = drIrb["PROTOCOL_NUM"].ToString();
			bool bRenewal = false;
			int newID = 0;

			drResults["ProtocolNumber"] = protoNumber;
			drResults["Action"] = "Add New Protocol";

			string sql = "";

			if (drIrb["PREV_PROTOCOL_NUM"] != System.DBNull.Value)
				bRenewal = true;

			if (bRenewal)
				sql = "INSERT INTO Protocol (SPID,ProtocolNumber,LongTitle,IrbApproval,IrbActivation,Incomplete," +
					"ConsentToMR,MedicalRecords,NurseBinder,PCategory,Phase,PSite,CreatedBy,CreatedWhen)" +
					"VALUES(@SPID,@ProtocolNumber,@LongTitle,@IrbApproval,@IrbActivation,@Incomplete," +
					"@ConsentToMR,@MedicalRecords,@NurseBinder,@PCategory,@Phase,@PSite,@CreatedBy,@CreatedWhen)";
			else
				sql = "INSERT INTO Protocol (SPID,ProtocolNumber,LongTitle,IrbApproval,IrbActivation,Incomplete," +
					"ConsentToMR,MedicalRecords,NurseBinder,PCategory,Phase,PSite,CreatedBy,CreatedWhen,IrbReviewCode)" +
					"VALUES(@SPID,@ProtocolNumber,@LongTitle,@IrbApproval,@IrbActivation,@Incomplete," +
					"@ConsentToMR,@MedicalRecords,@NurseBinder,@PCategory,@Phase,@PSite," +
					"@CreatedBy,@CreatedWhen,@IrbReviewCode)";


			int spid = GetNextSpid();
			if (spid < 1)
				return 0;

			SqlConnection conn = DataAccess.GetSqlConnection();
			SqlCommand cmd = new SqlCommand(sql, conn);

			SqlParameter param = new SqlParameter("@SPID", SqlDbType.Int, 0, "SPID");
			param.Value = spid;
			cmd.Parameters.Add(param);

			param = new SqlParameter("@ProtocolNumber", SqlDbType.NVarChar, 50, "ProtocolNumber");
			param.Value = protoNumber;
			cmd.Parameters.Add(param);

			string title = Microsoft.VisualBasic.Strings.StrConv(drIrb["TITLE"].ToString(), VbStrConv.ProperCase, 1);
			param = new SqlParameter("@LongTitle", SqlDbType.NVarChar, 1000, "LongTitle");
			param.Value = title;
			cmd.Parameters.Add(param);

			param = new SqlParameter("@IrbApproval", SqlDbType.DateTime, 0, "IrbApproval");
			param.Value = (DateTime)drIrb["APPROVAL_DT"];
			cmd.Parameters.Add(param);

			param = new SqlParameter("@IrbActivation", SqlDbType.DateTime, 0, "IrbActivation");
			if (drIrb["ACTIVATION_DT"] == System.DBNull.Value)
				param.Value = System.DBNull.Value;
			else
				param.Value = (DateTime)drIrb["ACTIVATION_DT"];
			cmd.Parameters.Add(param);

			param = new SqlParameter("@Incomplete", SqlDbType.Bit, 0, "Incomplete");
			param.Value = 1;
			cmd.Parameters.Add(param);

			param = new SqlParameter("@ConsentToMR", SqlDbType.Int, 0, "ConsentToMR");
			param.Value = 1;
			cmd.Parameters.Add(param);

			param = new SqlParameter("@MedicalRecords", SqlDbType.Int, 0, "MedicalRecords");
			param.Value = 1;
			cmd.Parameters.Add(param);

			param = new SqlParameter("@NurseBinder", SqlDbType.Int, 0, "NurseBinder");
			param.Value = 1;
			cmd.Parameters.Add(param);

			param = new SqlParameter("@PCategory", SqlDbType.Int, 0, "PCategory");
			param.Value = 1;
			cmd.Parameters.Add(param);

			param = new SqlParameter("@Phase", SqlDbType.Int, 0, "Phase");
			param.Value = 1;
			cmd.Parameters.Add(param);

			param = new SqlParameter("@PSite", SqlDbType.Int, 0, "PSite");
			param.Value = 1;
			cmd.Parameters.Add(param);

			param = new SqlParameter("@CreatedBy", SqlDbType.NVarChar, 50, "CreatedBy");
			param.Value = LoginForm.ApplicationSecurity.ApplicationUser.Name;
			cmd.Parameters.Add(param);

			param = new SqlParameter("@CreatedWhen", SqlDbType.DateTime, 0, "CreatedWhen");
			param.Value = DateTime.Today;
			cmd.Parameters.Add(param);

			if (!bRenewal)
			{
				param = new SqlParameter("@IrbReviewCode", SqlDbType.NVarChar, 5, "IrbReviewCode");
				param.Value = drIrb["REVIEW_CD"].ToString();
				cmd.Parameters.Add(param);
			}

			try
			{
				conn.Open();
				int rslt = cmd.ExecuteNonQuery();

				if (rslt == 1)
				{
					cmd.CommandText = "SELECT @@IDENTITY";
					object o = cmd.ExecuteScalar();
					if (o != null)
						newID = Convert.ToInt32(o);
					sb.Append("Add new protocol number " + protoNumber + " executed.");
					sb.Append("SPID # " + spid.ToString());
					MessageBox.Show(this, sb.ToString(), "Add New Protocol Executed", MessageBoxButtons.OK, MessageBoxIcon.Information);
					drResults["Result"] = "Executed";
					drResults["Message"] = sb.ToString();

				}
				else
				{
					sb.Append("Could not add new protocol number " + protoNumber);
					sb.Append(Environment.NewLine);
					sb.Append("ExecuteNonQuery returned 0");
					MessageBox.Show(this, sb.ToString(), "Add New Protocol Failed", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
					drResults["Result"] = "Failed";
					drResults["Message"] = sb.ToString();
				}
				drResults["ID"] = newID.ToString();
				return newID;
			}
			catch (Exception ex)
			{
				sb.Append("Could not add new protocol number " + protoNumber);
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Add New Protocol Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return 0;
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
				UnlockForm();
			}
		}

		private void UpdateProtocol(DataRow drIrb, string field, int id, DataRow drResults)
		{
			StringBuilder sb = new StringBuilder();
			StringBuilder sql = new StringBuilder();
			DateTime dtApproval = (DateTime)drIrb["APPROVAL_DT"];
			string protoNumber = drIrb["PROTOCOL_NUM"].ToString();
			bool bRenewal = false;

			if (drIrb["PREV_PROTOCOL_NUM"] != System.DBNull.Value)
				bRenewal = true;

			drResults["ID"] = id.ToString();
			drResults["IRBApproval"] = dtApproval;
			drResults["ProtocolNumber"] = protoNumber;
			drResults["Action"] = "Update " + field;
			sql.Append("UPDATE Protocol Set " + field + " = @irbApproval,ModifiedBy=@ModifiedBy,ModifiedWhen=@ModifiedWhen");

			if (!bRenewal && (field != "CrcTerminate"))
				sql.Append(",IrbReviewCode =@IrbReviewCode");

			sql.Append(" WHERE ID=" + id);

			SqlConnection conn = DataAccess.GetSqlConnection();
			SqlCommand cmd = new SqlCommand(sql.ToString(), conn);

			SqlParameter param = new SqlParameter("@irbApproval", SqlDbType.DateTime, 0, field);
			param.Value = dtApproval;
			cmd.Parameters.Add(param);

			param = new SqlParameter("@ModifiedBy", SqlDbType.NVarChar, 50, "ModifiedBy");
			param.Value = LoginForm.ApplicationSecurity.ApplicationUser.Name;
			cmd.Parameters.Add(param);

			param = new SqlParameter("@ModifiedWhen", SqlDbType.DateTime, 0, "ModifiedWhen");
			param.Value = DateTime.Today;
			cmd.Parameters.Add(param);

			if (!bRenewal)
			{
				param = new SqlParameter("@IrbReviewCode", SqlDbType.NVarChar, 5, "IrbReviewCode");
				param.Value = drIrb["REVIEW_CD"].ToString();
				cmd.Parameters.Add(param);
			}


			try
			{
				conn.Open();
				int rslt = cmd.ExecuteNonQuery();
				if (rslt < 1)
				{
					sb.Append("Could not update protocol number " + protoNumber);
					sb.Append(Environment.NewLine);
					sb.Append("ExecuteNonQuery returned 0");
					MessageBox.Show(this, sb.ToString(), "Update Protocol Failed", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
					drResults["Result"] = "Failed";
					drResults["Message"] = sb.ToString();
				}
				else
				{
					sb.Append("Update protocol number " + protoNumber + " executed.");
					MessageBox.Show(this, sb.ToString(), "Update Protocol Executed", MessageBoxButtons.OK, MessageBoxIcon.Information);
					drResults["Result"] = "Executed";
					drResults["Message"] = "Updated " + field + " with " + dtApproval.ToShortDateString();
				}

			}
			catch (Exception ex)
			{
				sb.Append("Could not update protocol number " + protoNumber);
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
				MessageBox.Show(this, sb.ToString(), "Update Protocol Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				drResults["Result"] = "Error";
				drResults["Message"] = sb.ToString();
			}
			finally
			{
				if (conn != null)
					conn.Dispose();
			}
		}

		private DataRow GetIrbDataRow(string protocolNumber, DateTime approvalDate)
		{
			DataRow dr;
			DataColumn[] dcp;
			DataTable dt = (DataTable)dg.DataSource;
			dcp = dt.PrimaryKey;
			if (dcp.Length == 0)
			{
				dcp = new DataColumn[2];
				dcp[0] = dt.Columns["PROTOCOL_NUM"];
				dcp[1] = dt.Columns["APPROVAL_DT"];
				dt.PrimaryKey = dcp;
			}
			object[] o = { protocolNumber, approvalDate };
			dr = dt.Rows.Find(o);

			return dr;
		}

		#region print
		private void btnPrint_Click(object sender, System.EventArgs e)
		{
			this.btnPrint.Enabled = false;
			this._pDoc = new PrintDocument();
			this._pDoc.DefaultPageSettings.Landscape = true;
			this._pDlg = new PrintDialog();
			this._pDlg.Document = this._pDoc;

			if (this._pDlg.ShowDialog(this) == DialogResult.Cancel)
				return;

			if (btnPrint.Text == "Print Analyzed Data")
				this._pDoc.PrintPage += new PrintPageEventHandler(_pDoc_PrintPage);
			else
				this._pDoc.PrintPage += new PrintPageEventHandler(_pDoc2_PrintPage);

			Cursor.Current = Cursors.WaitCursor;

			_page = 1;
			_startRow = 0;
			this._pDoc.Print();
			this.btnPrint.Enabled = true;
		}

		private void _pDoc2_PrintPage(object sender, PrintPageEventArgs e)
		{
			StringBuilder sb = new StringBuilder();
			Graphics gfx = e.Graphics;
			float pageBottom = gfx.VisibleClipBounds.Height;
			float pageWidth = gfx.VisibleClipBounds.Width;
			float leftMargin = 25;
			float topMargin = 25;

			Font prntFont = new Font("Arial", 10);
			Font titleFont = new Font("Arial", 14, FontStyle.Bold);
			Font subTitleFont = new Font("Arial", 12);
			Font ColumnHeadFont = new Font("Arial", 10, FontStyle.Bold);

			Pen pen = new Pen(Color.Black);

			//get the line height with some padding
			float lineHeight = prntFont.GetHeight(e.Graphics) + 2;
			float adjLineHeight = 0;

			//set the drawing to the top left of the page
			float yPos = topMargin;
			float xPos = leftMargin;

			//print page #
			string page = "Page " + this._page;
			Forms.PrintingUtils.PrintCenter(page, e, prntFont, pageBottom - lineHeight);

			#region title
			StringBuilder sbCrit = new StringBuilder();

			if (rdoDates.Checked)
			{
				sbCrit.Append("Criteria: by date - ");
				sbCrit.Append("Begin Date: " + BeginDate.Value.ToShortDateString() + "  End Date: " +
					EndDate.Value.ToShortDateString());
			}
			else
			{
				sbCrit.Append("Criteria: Protocol Number - " + Number.Text);
			}


			//print title and subtitle
			if (this._page == 1)
			{
				Forms.PrintingUtils.PrintCenter("GCRC - IRB Data Transfer - Results Data Report", e, titleFont, yPos); yPos += titleFont.GetHeight(e.Graphics);
				Forms.PrintingUtils.PrintCenter(sbCrit.ToString(), e, subTitleFont, yPos);
				yPos += subTitleFont.GetHeight(e.Graphics) + 2;
			}
			else
			{
				sb.Append("GCRC - IRB Data Transfer - Results Data Report - continued");
				Forms.PrintingUtils.PrintCenter(sb.ToString(), e, subTitleFont, yPos);
				yPos += subTitleFont.GetHeight(e.Graphics);
				Forms.PrintingUtils.PrintCenter(sbCrit.ToString(), e, subTitleFont, yPos);
				yPos += subTitleFont.GetHeight(e.Graphics) + 2;
			}
			yPos += lineHeight;
			#endregion title

			#region heading
			//print heading
			float c1 = 2; //+88
			float c2 = 90; //+110
			float c3 = 200; //+130
			float c4 = 330; //+100			
			float c5 = 430; //350

			float c1W = 88; //protocol number
			float c2W = 110; //review type
			float c3W = 130; //status
			float c4W = 100; //message 
			float c5W = 420; //message 

			RectangleF rec = new RectangleF();

			gfx.DrawRectangle(Pens.Black, 0, yPos, (pageWidth - 25), lineHeight);
			gfx.FillRectangle(Brushes.LightBlue, 0, yPos, pageWidth - 25, lineHeight);

			Forms.PrintingUtils.SetRectangle(ref rec, c1, yPos, lineHeight, c1W);
			gfx.DrawString("Protocol ID", ColumnHeadFont, Brushes.Black, rec);
			Forms.PrintingUtils.SetRectangle(ref rec, c2, yPos, lineHeight, c2W);
			gfx.DrawString("Protocol Number", ColumnHeadFont, Brushes.Black, rec);
			Forms.PrintingUtils.SetRectangle(ref rec, c3, yPos, lineHeight, c3W);
			gfx.DrawString("Update Type", ColumnHeadFont, Brushes.Black, rec);
			Forms.PrintingUtils.SetRectangle(ref rec, c4, yPos, lineHeight, c4W);
			gfx.DrawString("Update Result", ColumnHeadFont, Brushes.Black, rec);
			Forms.PrintingUtils.SetRectangle(ref rec, c5, yPos, lineHeight, c5W);
			gfx.DrawString("Message", ColumnHeadFont, Brushes.Black, rec);

			yPos += lineHeight;
			#endregion heading

			//get the number of lines we can print
			int maxLines = (int)Math.Ceiling((double)((pageBottom - yPos) / lineHeight));
			maxLines -= 2;
			int curLine = 0;

			StringFormat fmt = new StringFormat();
			fmt.Trimming = StringTrimming.EllipsisCharacter;

			DataTable dt = this._dtResults;

			DataRow dr;
			string message = "";
			int numLines = 0;
			//int numLines2 = 0;
			for (int row = _startRow; row < dt.Rows.Count; row++)
			{
				dr = dt.Rows[row];

				//message could be more than one line so get the number of lines
				message = dr["Message"].ToString();
				numLines = Forms.PrintingUtils.GetNumberLines(message, e.Graphics, prntFont, c5W, 0, lineHeight);

				if (curLine + numLines >= maxLines)
				{
					_startRow = row;
					this._page++;
					e.HasMorePages = true;
					return;
				}

				adjLineHeight = lineHeight * numLines;
				Forms.PrintingUtils.SetRectangle(ref rec, c1, yPos, adjLineHeight, c1W);
				gfx.DrawString(dr["ID"].ToString(), prntFont, Brushes.Black, rec, fmt);
				Forms.PrintingUtils.SetRectangle(ref rec, c2, yPos, adjLineHeight, c2W);
				gfx.DrawString(dr["ProtocolNumber"].ToString(), prntFont, Brushes.Black, rec, fmt);
				Forms.PrintingUtils.SetRectangle(ref rec, c3, yPos, adjLineHeight, c3W);
				gfx.DrawString(dr["Action"].ToString(), prntFont, Brushes.Black, rec, fmt);
				Forms.PrintingUtils.SetRectangle(ref rec, c4, yPos, adjLineHeight, c4W);
				gfx.DrawString(dr["Result"].ToString(), prntFont, Brushes.Black, rec, fmt);
				Forms.PrintingUtils.SetRectangle(ref rec, c5, yPos, adjLineHeight, c5W);
				gfx.DrawString(message, prntFont, Brushes.Black, rec, fmt);


				gfx.DrawRectangle(Pens.Black, 0, yPos, (pageWidth - 25), adjLineHeight);
				yPos += adjLineHeight;
				curLine += numLines;
			}
		}

		private void _pDoc_PrintPage(object sender, PrintPageEventArgs e)
		{
			StringBuilder sb = new StringBuilder();
			Graphics gfx = e.Graphics;
			float pageBottom = gfx.VisibleClipBounds.Height;
			float pageWidth = gfx.VisibleClipBounds.Width;
			float leftMargin = 25;
			float topMargin = 25;

			Font prntFont = new Font("Arial", 10);
			Font titleFont = new Font("Arial", 14, FontStyle.Bold);
			Font subTitleFont = new Font("Arial", 12);
			Font ColumnHeadFont = new Font("Arial", 10, FontStyle.Bold);

			Pen pen = new Pen(Color.Black);

			//get the line height with some padding
			float lineHeight = prntFont.GetHeight(e.Graphics) + 2;
			float adjLineHeight = 0;

			//set the drawing to the top left of the page
			float yPos = topMargin;
			float xPos = leftMargin;

			//print page #
			string page = "Page " + this._page;
			Forms.PrintingUtils.PrintCenter(page, e, prntFont, pageBottom - lineHeight);

			#region title
			StringBuilder sbCrit = new StringBuilder();

			if (rdoDates.Checked)
			{
				sbCrit.Append("Criteria: by date - ");
				sbCrit.Append("Begin Date: " + BeginDate.Value.ToShortDateString() + "  End Date: " +
					EndDate.Value.ToShortDateString());
			}
			else
			{
				sbCrit.Append("Criteria: Protocol Number - " + Number.Text);
			}


			//print title and subtitle
			if (this._page == 1)
			{
				Forms.PrintingUtils.PrintCenter("GCRC - IRB Data Transfer - Analyzed Data Report", e, titleFont, yPos);
				yPos += titleFont.GetHeight(e.Graphics);
				Forms.PrintingUtils.PrintCenter(sbCrit.ToString(), e, subTitleFont, yPos);
				yPos += subTitleFont.GetHeight(e.Graphics) + 2;
			}
			else
			{
				sb.Append("GCRC - IRB Data Transfer - Analyzed Data Report - continued");
				Forms.PrintingUtils.PrintCenter(sb.ToString(), e, subTitleFont, yPos);
				yPos += subTitleFont.GetHeight(e.Graphics);
				Forms.PrintingUtils.PrintCenter(sbCrit.ToString(), e, subTitleFont, yPos);
				yPos += subTitleFont.GetHeight(e.Graphics) + 2;
			}
			yPos += lineHeight;
			#endregion title

			#region heading
			//print heading
			float c1 = 2; //+115
			float c2 = 120; //+105
			float c3 = 225; //+100			
			float c4 = 325; //375
			float c5 = 700; //350

			float c1W = 115; //protocol number
			float c2W = 105; //review type
			float c3W = 100; //status
			float c4W = 375; //message 
			float c5W = 350; //message 

			RectangleF rec = new RectangleF();

			gfx.DrawRectangle(Pens.Black, 0, yPos, (pageWidth - 25), lineHeight);
			gfx.FillRectangle(Brushes.LightBlue, 0, yPos, pageWidth - 25, lineHeight);

			Forms.PrintingUtils.SetRectangle(ref rec, c1, yPos, lineHeight, c1W);
			gfx.DrawString("Protocol Number", ColumnHeadFont, Brushes.Black, rec);
			Forms.PrintingUtils.SetRectangle(ref rec, c2, yPos, lineHeight, c2W);
			gfx.DrawString("Review Type", ColumnHeadFont, Brushes.Black, rec);
			Forms.PrintingUtils.SetRectangle(ref rec, c3, yPos, lineHeight, c3W);
			gfx.DrawString("Status", ColumnHeadFont, Brushes.Black, rec);
			Forms.PrintingUtils.SetRectangle(ref rec, c4, yPos, lineHeight, c4W);
			gfx.DrawString("Message", ColumnHeadFont, Brushes.Black, rec);
			Forms.PrintingUtils.SetRectangle(ref rec, c5, yPos, lineHeight, c5W);
			gfx.DrawString("Comparing Data (Existing / IRB)", ColumnHeadFont, Brushes.Black, rec);

			yPos += lineHeight;
			#endregion heading

			//get the number of lines we can print
			int maxLines = (int)Math.Ceiling((double)((pageBottom - yPos) / lineHeight));
			maxLines -= 2;
			int curLine = 0;

			StringFormat fmt = new StringFormat();
			fmt.Trimming = StringTrimming.EllipsisCharacter;

			DataTable dt = this._dtAnalyzed;

			DataRow dr;
			string message = "";
			string comparingData = "";
			int numLines = 0;
			int numLines2 = 0;
			for (int row = _startRow; row < dt.Rows.Count; row++)
			{
				dr = dt.Rows[row];

				//message could be more than one line so get the number of lines
				message = dr["Message"].ToString();
				numLines = Forms.PrintingUtils.GetNumberLines(message, e.Graphics, prntFont, c4W, 0, lineHeight);
				if (numLines == 0)
					numLines = 1;
				comparingData = dr["ComparingData"].ToString();
				numLines2 = Forms.PrintingUtils.GetNumberLines(comparingData, e.Graphics, prntFont, c4W, 0, lineHeight);
				if (numLines2 > numLines)
					numLines = numLines2;

				if (curLine + numLines >= maxLines)
				{
					_startRow = row;
					this._page++;
					e.HasMorePages = true;
					return;
				}

				adjLineHeight = lineHeight * numLines;
				Forms.PrintingUtils.SetRectangle(ref rec, c1, yPos, adjLineHeight, c1W);
				gfx.DrawString(dr["ProtocolNumber"].ToString(), prntFont, Brushes.Black, rec, fmt);
				Forms.PrintingUtils.SetRectangle(ref rec, c2, yPos, adjLineHeight, c2W);
				gfx.DrawString(dr["ReviewType"].ToString(), prntFont, Brushes.Black, rec, fmt);
				Forms.PrintingUtils.SetRectangle(ref rec, c3, yPos, adjLineHeight, c3W);
				gfx.DrawString(dr["Status"].ToString(), prntFont, Brushes.Black, rec, fmt);
				Forms.PrintingUtils.SetRectangle(ref rec, c4, yPos, adjLineHeight, c4W);
				gfx.DrawString(message, prntFont, Brushes.Black, rec, fmt);

				Forms.PrintingUtils.SetRectangle(ref rec, c5, yPos, adjLineHeight, c5W);
				gfx.DrawString(comparingData, prntFont, Brushes.Black, rec, fmt);

				gfx.DrawRectangle(Pens.Black, 0, yPos, (pageWidth - 25), adjLineHeight);
				yPos += adjLineHeight;
				curLine += numLines;
			}
		}
		#endregion print

		private void btnExit_Click(object sender, System.EventArgs e)
		{
			this.Close();
		}

	}

	public enum ReviewType
	{
		Initial,
		FirstContinue,
		SecondContiue,
		Terminate,
		UnAssigned,
		Continue,
		Rewrite
	}
}
